# Copyright 2012 Google Inc. All Rights Reserved.

"""One-line documentation for models module.

A detailed description of models.
"""

__author__ = 'pluu@google.com (Phong Luu)'


from google.appengine.ext import db
from sql_connector import SQLConnector
import simplejson
import logging
from google.appengine.ext.db import stats
from google.appengine.api import memcache



class OAuthUser(db.Model):
  """Stores the credentials of the user for the YT upload"""
  userid = db.StringProperty()
  username = db.StringProperty()
  password = db.StringProperty()


class APIRequestQueue(db.Model):
  page_index = db.IntegerProperty()
  requested_date = db.DateTimeProperty(auto_now_add=True)


class PremiumPartners(db.Model):
  """Model containing all channel names, also used in MR."""
  channel_name = db.StringProperty()
  last_updated = db.DateTimeProperty(auto_now_add=True)
  alerts = db.StringProperty()
  num_of_new_videos = db.IntegerProperty(default=0)


class WhiteLabelPlaylistBuckets(db.Model):
  """This model holds only actual YT playlist info, that is to be used in the
  sync process"""
  is_available = db.BooleanProperty(default=True)
  playlistid = db.StringProperty()
  playlist_name = db.StringProperty()
  date_assigned = db.DateTimeProperty()
  date_synced_to_youtube = db.DateTimeProperty()


class Playlists(db.Model):
  """Defination of Youtube Playlists, also used in MR"""
  playlistid = db.StringProperty()
  playlist_name = db.StringProperty()
  last_updated = db.DateTimeProperty(auto_now_add=True)
  videotext = db.TextProperty()
  alerts = db.StringProperty()
  num_of_new_videos = db.IntegerProperty(default=0)
  @property
  def videoids(self):
    return self.videotext.split(',')


class User(db.Model):
  username = db.StringProperty() # this will be an email address
  added_date = db.DateTimeProperty(auto_now_add=True)
  last_accessed = db.DateTimeProperty()


class Organization(db.Model):
  STATUS = ['ENABLED', 'DISABLED']
  name = db.StringProperty()
  created = db.DateTimeProperty(auto_now_add=True)
  last_yt_sync_ran = db.DateTimeProperty()
  description = db.StringProperty()
  #disabled initially to ensure setup is complete.
  status = db.StringProperty(default=STATUS[0])
  def org_key(self):
    return str(self.key())


class UserAccessRights(db.Model):
  #a curator can only exist in one campaign
  username = db.ReferenceProperty(User)
  organization = db.ReferenceProperty(Organization)


class Tags(db.Model):
  """Models for tags for each organization.

  Attributes:
    organization: Foreign key to Organization.
    name: Name for this tag.
    associate_to_playlist: Indicates if this tag is attached to a live YT
        playlist(PL) account. Any video that has this tag will get
        sync'd to a pre created youtube account.
    playlistid: This is only filled if this tag is to be attached to a live PL.
    videoids: A list of videoids that are to be sync'd to the live PL.
    last_updated: Datetime stamp.
  """
  PLAYLIST_VIDEO_THRESHOLD = 195 # Videos allowed in a YT playlist
  organization = db.ReferenceProperty(Organization)
  name = db.StringProperty()
  associate_to_playlist = db.BooleanProperty(default=False)
  playlistid = db.StringProperty()
  videoids = db.TextProperty()
  last_updated = db.DateTimeProperty()

  @property
  def videoidlist(self):
    return simplejson.loads(self.videoids or "[]")

  def AddVideo(self, videoid):
    """Adds a videoid to list if tag is flagged as playlist."""
    logging.info('adding %s into %s', videoid, self.name)

    x = self.videoidlist
    logging.info('length of playlist %s', len(self.videoidlist))
    if len(x) < self.PLAYLIST_VIDEO_THRESHOLD:
      x.append(videoid)
    else:
      raise ValueError('playlist threshold reached for %s' % self.name)

    self.videoids = simplejson.dumps(x)

  def put(self):
    if self.associate_to_playlist and not self.playlistid:
      raise ValueError('refushing to save without assigned yt plid')
    else:
      super(Tags, self).put()


  def DeleteVideo(self, videoid):
    """Delete a videoid off list if tag is flagged as playlist."""
    logging.info('removing %s into %s', videoid, self.name)
    x = self.videoidlist
    for i, storedvideoid in enumerate(x):
      if storedvideoid == videoid:
        x.pop(i)
    self.videoids = simplejson.dumps(x)


class VideosToVerify(db.Model):
  """All videoids in cloudsql are also stored here for mapreduce purposes."""
  videoid = db.StringProperty()
  date_added = db.DateTimeProperty(auto_now_add=True)
  last_checked = db.DateTimeProperty()
  flagged_delete = db.BooleanProperty(default=False)


class DeletedVideos(db.Model):
  """Stores videoids that have been deleted from system."""
  videoid = db.StringProperty()
  date_deleted = db.DateTimeProperty(auto_now_add=True)


class PartnerAssociation(db.Model):
  """Determines which partners an organisation has access to curate."""
  organization = db.ReferenceProperty(Organization)
  partners = db.ReferenceProperty(PremiumPartners)


class PlaylistAssociation(db.Model):
  """Determines which playlists an organisation has access to curate."""
  organization = db.ReferenceProperty(Organization)
  playlist = db.ReferenceProperty(Playlists)


class Videos(SQLConnector):
  # This list of country codes is only a subset available within youtube. This
  # list represents only monetized countries.
  ALL_YT_COUNTRIES = sorted([
      'AR', 'AU', 'BR', 'CA', 'CZ', 'FR', 'DE', 'GB', 'HK',
      'IN', 'IE', 'IL', 'IT', 'JP', 'MX', 'NL', 'NZ', 'PL',
      'RU', 'ZA', 'KR', 'ES', 'SE', 'TW', 'US'])

  TABLE_NAME = 'videos'

  def __init__(self):
    SQLConnector.__init__(self)

  def UpdateVideoDict(self, video_meta):
    """Adds allowed list of GEOs available for a video.

    Args:
      video_meta: A dictionary containing all meta about a single YT video.

    Returns:
      A dictionary containing field, value pairs where the field name must match
      the column names of the youtubedb.videos tbl.
    """
    if video_meta.get('relationship', '') == 'allow':
      allowed_geos_list = str(video_meta.get('country_list', []))
    else:
      #do an invert of the deny list
      allowed_geos = set(self.ALL_YT_COUNTRIES).difference(
          video_meta.get('country_list', []))
      allowed_geos_list = list(allowed_geos)

    video_meta.update({'allowed_geos_list': simplejson.dumps(allowed_geos_list)})

    if video_meta.has_key('relationship'):
      video_meta.pop('relationship')

    if video_meta.has_key('country_list'):
      video_meta.pop('country_list')

    video_meta['country_restriction'] = simplejson.dumps(
        video_meta['country_restriction'])
    return video_meta

  def InsertVideos(self, videos):
    if type(videos).__name__ == 'list':
      for v in videos:
        try:
          self.InsertData(self.UpdateVideoDict(v), self.TABLE_NAME)
        except Exception, e:
          logging.info(e)

    elif type(videos).__name__ == 'dict':
      try:
        self.InsertData(self.UpdateVideoDict(videos), self.TABLE_NAME)
      except Exception, e:
        logging.info(e)

    return True

  def UpdateVideos(self, videos):
    if type(videos).__name__ == 'list':
      for v in videos:
        if v.has_key('published'):
          v.pop('published')

        try:
          self.UpdateTable(
              set_values=self.UpdateVideoDict(v),
              where_filters={'videoid': v['videoid']},
              table=self.TABLE_NAME)
        except Exception, e:
          logging.info(e)

    elif type(videos).__name__ == 'dict':
      if videos.has_key('published'):
        videos.pop('published')
      try:
        self.UpdateTable(
            set_values=self.UpdateVideoDict(videos),
            where_filters={'videoid': videos['videoid']},
            table=self.TABLE_NAME)
      except Exception, e:
        logging.info(e)
    return True


class Curator(SQLConnector):
  """A layer on top of SQL to handle the playlist curation complexities."""
  #key helps prevent video being added to same org+ playlist bucket
  CURATED_VIDEO_KEY_NAME = '%s_%s_%s'
  QUERY_FETCH_LIMIT = 200
  TABLE_NAME = 'curated_videos'

  def __init__(self, organization):
    SQLConnector.__init__(self)
    self.organization = organization
    self.curated_video_table = {}

    if self.organization.status == 'DISABLED':
      raise ValueError('Campaign %s has been disabled' % self.organization.name)

    self.total_videos = 0
    self.playlist_tags = Tags.all().filter('organization', self.organization)
    self.playlist_tags.filter('associate_to_playlist', True).fetch(100)

  def _AddPLTags(self, videoid, tags):
    for pl in self.playlist_tags:
      for tag in tags:
        if tag == pl.name:
          logging.info('adding to tag %s', pl.name)
          pl.AddVideo(videoid)
          pl.put()

  def _DeletePLTags(self, videoid, tags):
    #if there are no entities with assigned to playlist, this will drop
    for pl in self.playlist_tags:
      for tag in tags:
        if tag == pl.name:
          logging.info('adding to tag %s', pl.name)
          pl.DeleteVideo(videoid)
          pl.put()

  def _UpdatePlaylistTags(self, videoid, prev_tags, new_tags):
    add = set(new_tags).difference(prev_tags)
    delete = set(prev_tags).difference(new_tags)

    if add:
      self._AddPLTags(videoid, add)

    if delete:
      self._DeletePLTags(videoid, delete)

    logging.info('tags to remove %s', delete)
    logging.info('tags to add %s', add)

  def UpdateTags(self, videoid, tags):
    """Inserts or updates tags data into curation table.

    Args:
      videoid: Youtube videoid
      tags: list of tag names.
    """
    key_name = '%s_%s' % (self.organization.key(), videoid)

    self.curated_video = self.GetTableData(
        self.TABLE_NAME, {'key_name': key_name})

    self.curated_video = self.curated_video[0] if self.curated_video else None


    insert_dict = {'key_name': key_name,
                   'videoid': videoid,
                   'tags': ','.join(tags),
                   'org_key': str(self.organization.key())}

    if self.curated_video:
      prev_tags = self.curated_video['tags'].split(',')
      self._UpdatePlaylistTags(videoid, prev_tags, tags)
      #do update
      if tags:
        self.UpdateTable(insert_dict, {'videoid': videoid}, self.TABLE_NAME)
      else:
        self.DeleteData({'key_name': key_name}, self.TABLE_NAME)

    else:
      prev_tags = []
      self._UpdatePlaylistTags(videoid, prev_tags, tags)
      if tags:
        self.InsertData(insert_dict, self.TABLE_NAME)
      else:
        logging.info('nothing to insert')

    return self

  def FilterVideos(
      self, partners, playlists,q=None,
      tag_list=[], geo_filters=[], order_by='published', page_index=1):
    """Fetch both curated, and uncurated videos into the results.

    Cloud SQL has connection limitations where any SQL executed must be complete
    within 60secs. This is not an appengine limitation and will cause timeouts
    even when executing a query using appengine backend tasks.

    The SQL used in this method originally included LEFT JOINS which made the
    method simplier, but when curated_videos contain more than 10K rows, this
    query no longer returns results before the timeout period. Therefore this
    function needs to be refactored to only query one table and programmically
    join the results.


    original query:
    SELECT
      DATE_FORMAT(v.published, "%%Y-%%m-%%d %%h:%%m:%%s") as
      published,
      v.title, v.description, v.uploader, v.videoid,
      v.thumbnail, v.view_count,
      v.country_restriction,
      cv.tags
    FROM `videos` as v
    LEFT JOIN curated_videos as cv
        ON cv.videoid = v.videoid
        AND cv.org_key = "%(ORG_KEY)s"
    WHERE


    Args:
      partner: list of partner/channel names to filter.
      order_by: Sort order of videos table.
      query_text: Video search string for specific video content.
 
    Returns:
      List of videos that match user specified criterias.
    """
    offset = self.QUERY_FETCH_LIMIT * page_index - self.QUERY_FETCH_LIMIT

    where_sql = []
    if tag_list:
      #if user is looking for a tag then we know, that they want to look at
      #curated content only. Otherwise they will likely to be searching for
      #uncurated videos.
      self.fetched_videos = self.GetCuratedVideos(
          q, tag_list, partners, geo_filters, page_index)

      self.total_videos = len(self.fetched_videos)
    
    else:

      where_sql = self._BuildWhereList(q, partners, geo_filters)

      if playlists:
        v = []
        p = Playlists.get_by_key_name(playlists)
        for x in p:
          v += x.videoids

        video_str = ['"%s"' % vid for vid in v]
        where_sql.append('v.videoid IN (%s)' % ','.join(video_str))


      if order_by == 'published':
        order_sql = 'ORDER BY published DESC'
      else:
        order_sql = 'ORDER BY view_count DESC'

      base_query = (
          """
          SELECT
            DATE_FORMAT(v.published, "%%Y-%%m-%%d %%h:%%m:%%s") as
            published,
            v.title, v.description, v.uploader, v.videoid,
            v.thumbnail, v.view_count,
            v.country_restriction,
            '' as tags
          FROM videos as v
          WHERE %(WHERE_CLAUSE)s
          %(ORDER_BY)s, videoid
          LIMIT %(OFFSET)s, %(FETCH_LIMIT)s
          """)
      query_dict = {
          'WHERE_CLAUSE': ' AND '.join(where_sql),
          'FETCH_LIMIT': self.QUERY_FETCH_LIMIT,
          'OFFSET': offset,
          'ORDER_BY': order_sql}
      sql_stm = base_query % query_dict
      
      self.fetched_videos = memcache.get(sql_stm)
      if not self.fetched_videos:
        self.fetched_videos = self._Execute(sql_stm)
        memcache.set(sql_stm, self.fetched_videos, 60 * 20)

      self.total_videos = len(self.fetched_videos)
      #TODO: separate this part out into method.
      if self.fetched_videos:
        videoid_list = ["'%s'" % v['videoid'] for v in self.fetched_videos]
        left_join_curated = (
            """
            SELECT videoid, tags
            FROM curated_videos
            WHERE videoid in (%s)
            AND org_key = "%s"
            """) % (','.join(videoid_list), self.organization.key())
        curated = self._Execute(left_join_curated)
        self.curated_video_table = dict([(v['videoid'], v['tags']) for v in curated])

  def FetchVideos(self):
    """Yields video meta data and adds derived fields."""
    for v in self.fetched_videos:
      try:
        geo_dict = simplejson.loads(v['country_restriction'])
        v['geo_type'] = geo_dict.get('relationship', '')
        v['geos_list'] = geo_dict.get('$t', '').split(' ')
      except:
        pass

      if  self.curated_video_table.has_key(v['videoid']):
        v['tags_list'] = self.curated_video_table.get(v['videoid']).split(',')
      else:
        v['tags_list'] = v.get('tags', '').split(',') if v['tags'] else []

      yield v

  def _BuildWhereList(self, q, partners, geo_filters):
    where_clause = []
    if q:
      x = ("""
           (
           v.title like "%%%(SEARCH)s%%" OR
           v.description like "%%%(SEARCH)s%%")""") % {'SEARCH': q}
      where_clause.append(x)
    if partners:
      partner_filter = [
          '"%s"' % uploader for uploader in partners]
      where_clause.append('v.uploader IN (%s)' % ','.join(partner_filter))

    if geo_filters:
      for cc in geo_filters:
        where_clause.append(' allowed_geos_list like "%%%s%%"' % cc)

    return where_clause


  def GetCuratedVideos(self, q=None, tag_list=[], partners=[], geo_filters=[], page_index=1):
    """Gets a list of videos that have been curated.

    Args:
      q: Search query to filter curated content by.
      tag_list: A list of tag names.
      geo_filters: A list of 2 letter country codes.
      page_index: Page number to offset results by.
    """
    fetch_limit = 500
    offset = fetch_limit * page_index - fetch_limit

    where_clause = self._BuildWhereList(q, partners, geo_filters)

    if tag_list:
      or_tags = []
      for name in tag_list:
        or_tags.append('cv.tags like "%%%s%%"' % name)
      where_clause.append('(%s)' % ' OR '.join(or_tags))


    where_clause.append('org_key = "%s"' % self.organization.key())

    #TODO: apply filter by playlists
    sql = ("""
           SELECT
             cv.videoid,
             cv.tags,
             DATE_FORMAT(cv.modified_date, "%%Y-%%m-%%d") as modified_date,
             v.uploader,
             title,
             thumbnail,
             v.description,
             view_count,
             DATE_FORMAT(v.published, "%%Y-%%m-%%d") as published
           FROM curated_videos AS cv
           INNER JOIN videos AS v
             ON v.videoid = cv.videoid
           %(WHERE_CLAUSE)s
           ORDER BY cv.modified_date DESC
           LIMIT %(OFFSET)s, %(LIMIT)s
           """)
    query_dict = {'OFFSET': offset,
                  'LIMIT': fetch_limit,
                  'WHERE_CLAUSE': 'WHERE ' + ' AND '.join(where_clause)}

    sql_stm = sql % query_dict
    rows = memcache.get(sql)
    if not rows:
      logging.info('no cache doing query')
      rows = self._Execute(sql % query_dict)
      memcache.set(sql, rows, 60 * 10)
    return rows


class UsageStats(SQLConnector):
  STATS_METHOD = [
      'GetAvailablePlaylists',
      'GetTotalPartners',
      'GetTotalVideos',
      'GetTotalCuratedVideos']

  def __init__(self, organization=None):
    SQLConnector.__init__(self)
    self.organization = organization

  def GetNumCampaigns(self):
    pass
  def GetTotalPartners(self):
    sql = ("""
           SELECT COUNT(DISTINCT(uploader)) as 'Total unique partners'
           FROM videos""")
    return self._Execute(sql)[0]

  def GetTotalVideos(self):
    sql = ("""
           SELECT COUNT(*) as 'Total number of videos'
           FROM videos""")
    return self._Execute(sql)[0]
 
  def GetTotalCuratedVideos(self):
    sql = ("""
           SELECT COUNT(*) as 'Total number of curated videos'
           FROM curated_videos""")
    return self._Execute(sql)[0]

  def GetAvailablePlaylists(self):
    available_pls =  WhiteLabelPlaylistBuckets.all().filter(
        'is_available', True).count()
    return {'Unassigned YT playlists': available_pls}

  def GetAllStats(self):
    stats = {}
    for method in self.STATS_METHOD:
      func = getattr(self, method)
      stats.update(func())
    return stats


#TODO: add filters within date ranges.
class Reporting(SQLConnector):
  CACHE_EXPIRATION = 60 * 60  # 1hour

  def __init__(self, organization=None):
    SQLConnector.__init__(self)
    self.organization = organization
    if organization:
      partners = PartnerAssociation.all().filter(
          'organization', self.organization).fetch(1000)
      self.partners = ['"%s"' % partner.partners.channel_name  for partner in partners]

  def GetPartnerVideoCount(self, start_date=None, end_date=None):
    """Get total video count grouped by uploader."""
    where_clause = []
    if self.organization:
      where_clause.append('uploader IN (%s)' % ','.join(self.partners))

    sql = (
        """
        SELECT uploader as description, count(videoid) as total
        FROM videos
        %(WHERE_SQL)s
        GROUP BY uploader
        HAVING total > 0
        ORDER BY total DESC
        """)
    query_dict = {'WHERE_SQL': 'WHERE %s' % ' AND '.join(where_clause)}
    sql_stm = sql % query_dict
    return self._GetCache(sql_stm)


    return self._Execute(sql % query_dict)

  def GetPartnerCuratedVideoCount(self, start_date=None, end_date=None):
    """Get the number of curated videos grouped by uploader/partners."""
    where_clause = []
    if self.organization:
      where_clause.append('uploader IN (%s)' % ','.join(self.partners))
      where_clause.append('org_key = "%s"' % self.organization.key())

    sql = (
        """
        SELECT v.uploader as description, count(v.videoid) as total
        FROM curated_videos as cv
        INNER JOIN videos as v
            ON v.videoid = cv.videoid
        %(WHERE_SQL)s
        GROUP BY uploader
        HAVING total > 0
        ORDER BY total DESC
        """)
    query_dict = {'WHERE_SQL': 'WHERE %s' % ' AND '.join(where_clause)}
    sql_stm = sql % query_dict
    return self._GetCache(sql_stm)

  def _GetCache(self, sql_stm):
    rows = memcache.get(sql_stm)
    if not rows:
      rows = self._Execute(sql_stm)
      memcache.set(sql_stm, rows, self.CACHE_EXPIRATION)
    return rows


